package cl.foursoft.sirvicod.dao.implementacion.postgresql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cl.foursoft.sirvicod.dao.implementacion.DBBase;
import cl.foursoft.sirvicod.dao.interfaces.IPersona;
import cl.foursoft.sirvicod.dao.transferObject.PersonaTO;
import cl.foursoft.sirvicod.util.UtilLog;

public class PersonaDB extends DBBase implements IPersona{

	public PersonaDB(Connection connection){
		this.conn = connection;
	}	
	
	@Override
	public int guardarPersona(PersonaTO persona) {
		int resp = -1;		
		PreparedStatement pstmt = null;	

		String query = 	" INSERT INTO persona (rut, nombre) " + 
					    " VALUES (?, ?) ";
		
		try
		{
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, persona.getRut());
			pstmt.setString(2, persona.getNombre());
			
			resp = pstmt.executeUpdate();

			pstmt.close();
		}
		catch (SQLException sqle){
			UtilLog.registrar(sqle);
		}
		catch (Exception ex) {
			UtilLog.registrar(ex);
		}
		
		finally{
			if (pstmt != null) {
				try{
					pstmt.close();					
				} 
				catch (SQLException e) {
					UtilLog.registrar(e); 
					e.printStackTrace();
				}
			}
		}	
				
		return resp;
	}
	
	@Override
	public int guardarDatosPersona(PersonaTO persona) {
		int resp = -1;		
		PreparedStatement pstmt = null;	

		String query = 	" INSERT INTO datos_persona (rut, email, telefono) VALUES (?, ?, ?); ";
		
		try
		{
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, persona.getRut());
			pstmt.setString(2, persona.getEmail());
			pstmt.setString(3, persona.getTelefono());
			
			resp = pstmt.executeUpdate();

			pstmt.close();
		}
		catch (SQLException sqle){
			UtilLog.registrar(sqle);
		}
		catch (Exception ex) {
			UtilLog.registrar(ex);
		}
		
		finally{
			if (pstmt != null) {
				try{
					pstmt.close();					
				} 
				catch (SQLException e) {
					UtilLog.registrar(e); 
					e.printStackTrace();
				}
			}
		}	
				
		return resp;
	}
	
	@Override
	public int actualizarPersona(PersonaTO persona) {
		int resp = -1;		
		PreparedStatement pstmt = null;	

		String query = 	" UPDATE persona " +
						" SET nombre = ? " + 
					    " WHERE rut = ? ";
		
		try
		{
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, persona.getNombre());
			pstmt.setString(2, persona.getRut());
			
			resp = pstmt.executeUpdate();

			pstmt.close();
		}
		catch (SQLException sqle){
			UtilLog.registrar(sqle);
		}
		catch (Exception ex) {
			UtilLog.registrar(ex);
		}
		
		finally{
			if (pstmt != null) {
				try{
					pstmt.close();					
				} 
				catch (SQLException e) {
					UtilLog.registrar(e); 
					e.printStackTrace();
				}
			}
		}	
				
		return resp;
	}

	@Override
	public int actualizarDatosPersona(PersonaTO persona) {
		int resp = -1;		
		PreparedStatement pstmt = null;	

		String query = 	" UPDATE datos_persona SET email = ?, telefono = ? WHERE rut = ?; " ;
		
		try
		{
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, persona.getEmail());
			pstmt.setString(2, persona.getTelefono());
			pstmt.setString(3, persona.getRut());
			
			resp = pstmt.executeUpdate();

			pstmt.close();
		}
		catch (SQLException sqle){
			UtilLog.registrar(sqle);
		}
		catch (Exception ex) {
			UtilLog.registrar(ex);
		}
		
		finally{
			if (pstmt != null) {
				try{
					pstmt.close();					
				} 
				catch (SQLException e) {
					UtilLog.registrar(e); 
					e.printStackTrace();
				}
			}
		}	
				
		return resp;
	}

	@Override
	public PersonaTO obtenerPersonaPorRut(String rut)
	{
		PersonaTO resp = null;
		
		PreparedStatement pstmt = null;
		
		ResultSet rs = null;

		String query = 	" SELECT rut, nombre FROM persona WHERE rut = ? ";
		
		try
		{
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, rut);
			
			rs = pstmt.executeQuery();
			
			if(rs.next())
			{
				resp = new PersonaTO();
				
				resp.setRut(rs.getString("rut"));
				resp.setNombre(rs.getString("nombre"));
			}
			rs.close();
			pstmt.close();
		}

		catch (SQLException sqle){
			UtilLog.registrar(sqle);
		}
		catch (Exception ex) {
			UtilLog.registrar(ex);
		}
		
		finally{
			if (pstmt != null) {
				try{
					pstmt.close();					
				} 
				catch (SQLException e) {
					UtilLog.registrar(e); 
					e.printStackTrace();
				}
			}
		}	
				
		return resp;
	}

	@Override
	public PersonaTO obtenerPersonaFullPorRut(String rut) {
		PersonaTO resp = null;		
		PreparedStatement pstmt = null;		
		ResultSet rs = null;

		String query = 	" SELECT p.rut, p.nombre, dp.email, dp.telefono " +
						" FROM persona p " +
						" LEFT OUTER JOIN datos_persona dp ON dp.rut = p.rut " +
						" WHERE p.rut = ? ";
		
		try
		{
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, rut);
			
			rs = pstmt.executeQuery();
			
			if(rs.next())
			{
				resp = new PersonaTO();
				
				resp.setRut(rs.getString("rut"));
				resp.setNombre(rs.getString("nombre"));
				resp.setEmail(rs.getString("email"));
				resp.setTelefono(rs.getString("telefono"));
			}
			rs.close();
			pstmt.close();
		}

		catch (SQLException sqle){
			UtilLog.registrar(sqle);
		}
		catch (Exception ex) {
			UtilLog.registrar(ex);
		}
		
		finally{
			if (pstmt != null) {
				try{
					pstmt.close();					
				} 
				catch (SQLException e) {
					UtilLog.registrar(e); 
					e.printStackTrace();
				}
			}
		}	
				
		return resp;
	}

	@Override
	public PersonaTO obtenerPropietario(int numeroDepto) {
		PersonaTO resp = null;		
		PreparedStatement pstmt = null;		
		ResultSet rs = null;

		String query = 	" SELECT p.rut, p.nombre, dp.email, dp.telefono " +
						" FROM persona p " +
						" LEFT OUTER JOIN datos_persona dp ON dp.rut = p.rut " +
						" INNER JOIN departamento d ON d.rut_propietario = p.rut " +
						" WHERE d.numero_depto = ? ";
		
		try
		{
			pstmt = conn.prepareStatement(query);
			pstmt.setInt(1, numeroDepto);
			
			rs = pstmt.executeQuery();
			
			if(rs.next())
			{
				resp = new PersonaTO();
				
				resp.setRut(rs.getString("rut"));
				resp.setNombre(rs.getString("nombre"));
				resp.setEmail(rs.getString("email"));
				resp.setTelefono(rs.getString("telefono"));
			}
			rs.close();
			pstmt.close();
		}

		catch (SQLException sqle){
			UtilLog.registrar(sqle);
		}
		catch (Exception ex) {
			UtilLog.registrar(ex);
		}
		
		finally{
			if (pstmt != null) {
				try{
					pstmt.close();					
				} 
				catch (SQLException e) {
					UtilLog.registrar(e); 
					e.printStackTrace();
				}
			}
		}	
				
		return resp;
	}

	@Override
	public List<PersonaTO> obtenerResidentes(int numeroDepto) {
		List<PersonaTO> resp = null;		
		PreparedStatement pstmt = null;		
		ResultSet rs = null;

		String query = 	" SELECT p.rut, p.nombre, dp.email, dp.telefono " + 
					    " FROM persona p " +
					    " LEFT OUTER JOIN datos_persona dp ON dp.rut = p.rut " +
					    " INNER JOIN residente_depto rd ON rd.rut = p.rut " +
					    " INNER JOIN departamento d ON d.numero_depto = rd.numero_depto " +
					    " WHERE d.numero_depto = ? ";
		
		try
		{
			pstmt = conn.prepareStatement(query);
			pstmt.setInt(1, numeroDepto);
			
			rs = pstmt.executeQuery();
			
			resp = new ArrayList<PersonaTO>();
			
			while(rs.next())
			{
				PersonaTO persona = new PersonaTO();
				
				persona.setRut(rs.getString("rut"));
				persona.setNombre(rs.getString("nombre"));
				persona.setEmail(rs.getString("email"));
				persona.setTelefono(rs.getString("telefono"));
				
				resp.add(persona);
			}
			rs.close();
			pstmt.close();
		}

		catch (SQLException sqle){
			UtilLog.registrar(sqle);
		}
		catch (Exception ex) {
			UtilLog.registrar(ex);
		}
		
		finally{
			if (pstmt != null) {
				try{
					pstmt.close();					
				} 
				catch (SQLException e) {
					UtilLog.registrar(e); 
					e.printStackTrace();
				}
			}
		}	
				
		return resp;
	}

	@Override
	public int eliminarResidentes(int numeroDepto) {
		int resp = -1;		
		PreparedStatement pstmt = null;	

		String query = 	" DELETE FROM residente_depto " + 
					    " WHERE numero_depto = ? ";
		
		try
		{
			pstmt = conn.prepareStatement(query);
			pstmt.setInt(1, numeroDepto);
			
			resp = pstmt.executeUpdate();

			pstmt.close();
		}
		catch (SQLException sqle){
			UtilLog.registrar(sqle);
		}
		catch (Exception ex) {
			UtilLog.registrar(ex);
		}
		
		finally{
			if (pstmt != null) {
				try{
					pstmt.close();					
				} 
				catch (SQLException e) {
					UtilLog.registrar(e); 
					e.printStackTrace();
				}
			}
		}	
				
		return resp;
	}

	@Override
	public int guardarResidente(PersonaTO residente, int numeroDepto) {
		int resp = -1;		
		PreparedStatement pstmt = null;	

		String query = 	" INSERT INTO residente_depto (rut, numero_depto) " + 
					    " VALUES (?, ?) ";
		
		try
		{
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, residente.getRut());
			pstmt.setInt(2, numeroDepto);
			
			resp = pstmt.executeUpdate();

			pstmt.close();
		}
		catch (SQLException sqle){
			UtilLog.registrar(sqle);
		}
		catch (Exception ex) {
			UtilLog.registrar(ex);
		}
		
		finally{
			if (pstmt != null) {
				try{
					pstmt.close();					
				} 
				catch (SQLException e) {
					UtilLog.registrar(e); 
					e.printStackTrace();
				}
			}
		}	
				
		return resp;
	}

	@Override
	public boolean existePersona(String rut) {
		boolean resp = true;		
		PreparedStatement pstmt = null;		
		ResultSet rs = null;

		String query = 	" SELECT COUNT(rut) personas" +
						" FROM persona " +
						" WHERE rut = ? ";
		
		try
		{
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, rut);
			
			rs = pstmt.executeQuery();
			
			if(rs.next())
			{
				resp = rs.getInt("personas") > 0;
			}
			rs.close();
			pstmt.close();
		}

		catch (SQLException sqle){
			UtilLog.registrar(sqle);
		}
		catch (Exception ex) {
			UtilLog.registrar(ex);
		}
		
		finally{
			if (pstmt != null) {
				try{
					pstmt.close();					
				} 
				catch (SQLException e) {
					UtilLog.registrar(e); 
					e.printStackTrace();
				}
			}
		}	
				
		return resp;
	}
	
	@Override
	public boolean existenDatosPersona(String rut) {
		boolean resp = true;		
		PreparedStatement pstmt = null;		
		ResultSet rs = null;

		String query = 	" SELECT COUNT(rut) personas" +
						" FROM datos_persona " +
						" WHERE rut = ? ";
		
		try
		{
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, rut);
			
			rs = pstmt.executeQuery();
			
			if(rs.next())
			{
				resp = rs.getInt("personas") > 0;
			}
			rs.close();
			pstmt.close();
		}

		catch (SQLException sqle){
			UtilLog.registrar(sqle);
		}
		catch (Exception ex) {
			UtilLog.registrar(ex);
		}
		
		finally{
			if (pstmt != null) {
				try{
					pstmt.close();					
				} 
				catch (SQLException e) {
					UtilLog.registrar(e); 
					e.printStackTrace();
				}
			}
		}	
				
		return resp;
	}
	
	@Override
	public int guardarPersonaAutorizada(PersonaTO autorizado, int numeroDepto) {
		int resp = -1;		
		PreparedStatement pstmt = null;	

		String query = 	" INSERT INTO visita_autorizada (rut, numero_depto) " + 
					    " VALUES (?, ?) ";
		
		try
		{
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, autorizado.getRut());
			pstmt.setInt(2, numeroDepto);
			
			resp = pstmt.executeUpdate();

			pstmt.close();
		}
		catch (SQLException sqle){
			UtilLog.registrar(sqle);
		}
		catch (Exception ex) {
			UtilLog.registrar(ex);
		}
		
		finally{
			if (pstmt != null) {
				try{
					pstmt.close();					
				} 
				catch (SQLException e) {
					UtilLog.registrar(e); 
					e.printStackTrace();
				}
			}
		}	
				
		return resp;
	}

	@Override
	public List<PersonaTO> obtenerPersonasAutorizadasPorDepto(int depto) {
		List<PersonaTO> resp = null;		
		PreparedStatement pstmt = null;		
		ResultSet rs = null;

		String query = 	" SELECT p.rut, p.nombre, dp.email, dp.telefono " +
						" FROM visita_autorizada va " +
						" INNER JOIN persona p on (va.rut = p.rut) " +
						" LEFT OUTER JOIN datos_persona dp ON dp.rut = p.rut " +
						" WHERE va.numero_depto = ? ";
		
		try
		{
			pstmt = conn.prepareStatement(query);
			pstmt.setInt(1, depto);
			
			rs = pstmt.executeQuery();
			
			resp = new ArrayList<PersonaTO>();
			
			while(rs.next())
			{
				PersonaTO persona = new PersonaTO();
				
				persona.setRut(rs.getString("rut"));
				persona.setNombre(rs.getString("nombre"));
				persona.setEmail(rs.getString("email"));
				persona.setTelefono(rs.getString("telefono"));
				
				resp.add(persona);
			}
			rs.close();
			pstmt.close();
		}

		catch (SQLException sqle){
			UtilLog.registrar(sqle);
		}
		catch (Exception ex) {
			UtilLog.registrar(ex);
		}
		
		finally{
			if (pstmt != null) {
				try{
					pstmt.close();					
				} 
				catch (SQLException e) {
					UtilLog.registrar(e); 
					e.printStackTrace();
				}
			}
		}	
				
		return resp;
	}

	@Override
	public Boolean personaEstaBloqueada(String rut) 
	{
		Boolean resp = null;		
		PreparedStatement pstmt = null;		
		ResultSet rs = null;

		String query = 	" select count(rut) existe from persona_bloqueada WHERE rut = ? ";
		
		try
		{
			pstmt = conn.prepareStatement(query);
			pstmt.setString(1, rut);
			
			rs = pstmt.executeQuery();
			
			while(rs.next())
			{
				int count = rs.getInt("existe");
				
				if(count > 0)
					resp = true;
				else
					resp = false;
			}
			rs.close();
			pstmt.close();
		}

		catch (SQLException sqle){
			UtilLog.registrar(sqle);
		}
		catch (Exception ex) {
			UtilLog.registrar(ex);
		}
		
		finally{
			if (pstmt != null) {
				try{
					pstmt.close();					
				} 
				catch (SQLException e) {
					UtilLog.registrar(e); 
					e.printStackTrace();
				}
			}
		}	
				
		return resp;
	}

	@Override
	public int eliminarPersonasAutorizadas(int numeroDepto) {
		int resp = -1;		
		PreparedStatement pstmt = null;	

		String query = 	" DELETE FROM visita_autorizada " + 
					    " WHERE numero_depto = ? ";
		
		try
		{
			pstmt = conn.prepareStatement(query);
			pstmt.setInt(1, numeroDepto);
			
			resp = pstmt.executeUpdate();

			pstmt.close();
		}
		catch (SQLException sqle){
			UtilLog.registrar(sqle);
		}
		catch (Exception ex) {
			UtilLog.registrar(ex);
		}
		
		finally{
			if (pstmt != null) {
				try{
					pstmt.close();					
				} 
				catch (SQLException e) {
					UtilLog.registrar(e); 
					e.printStackTrace();
				}
			}
		}	
				
		return resp;
	}

	

}
